package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import util.GetTime;




public class QnaBoardDAO {
	
	public static void main(String[] args){
	
//		System.out.println(qnaDAO.insert(qb));
//		System.out.println(qnaDAO.updateTitleContents(qb));
//		System.out.println(qnaDAO.updateqbCount(qb));
//		System.out.println(qnaDAO.delete(qb));
//		System.out.println(qnaDAO.getTotalRows(qb));
//		System.out.println(qnaDAO.getQnaBoard(qb).getMid());
//		System.out.println(qnaDAO.getQnaBoardListj());
//		System.out.println(qnaDAO.getQnaBoardList().get(1));
		//1
//		System.out.println(((QnaBoard)qnaDAO.getQnaBoardList().get(1)).getMid());
		//2
//		QnaBoard gresult =  (QnaBoard)qnaDAO.getQnaBoardList().get(1);
//		System.out.println(gresult.getMid() + gresult.getQbcontent());
//		Vector list =getQnaBoardListPage(5,5);
//		String aaaa = (String)list.get(1) + list.get(2) + list.get(3) +list.get(4) +list.get(5) +list.get(6) +list.get(7);
//		System.out.print(aaaa);
		
	}
	/*******
	 * 데이터 insert메소드
	 * @param qb
	 * @return
	 **************/
		public boolean insert(QnaBoard qb){
			Connection conn= ConnectionManager.getConnection();
       //@@	String sql1="insert into qnaboard(mid, qbtitle, qbdate, qbcontent, qbcounter, qbrefno) values(?,?,now(),?,0,?)";
			String sql1="insert into qnaboard(qbno, mid, qbtitle, qbdate, qbcontent, qbcounter, qbrefno) values(qbseq.NEXTVAL,?,?,?,?,0,?)";
			String sql2="update qnaboard set qbrefno=qbno where qbrefno=0";
			try{
				PreparedStatement pstmt = conn.prepareStatement(sql1);
				PreparedStatement pstmt2 = conn.prepareStatement(sql2);
				pstmt.setString(1, qb.getMid());
				pstmt.setString(2, qb.getQbtitle());
				pstmt.setTimestamp(3, GetTime.nowTime);//##
				/*@@pstmt.setString(3, qb.getQbcontent());
				pstmt.setInt(4, qb.getQbrefno());*/
				pstmt.setString(4, qb.getQbcontent());
				pstmt.setInt(5, qb.getQbrefno());
				
				pstmt.executeUpdate();
				pstmt2.executeUpdate();
				conn.close();
				
				return true;
			} catch(Exception e){
				e.printStackTrace();
				return false;
			}finally{
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
		}
		
		/*******
		 * 글제목과 내용 수정하는 메소드
		 * @param qb
 			@return */
		public boolean updateTitleContents(QnaBoard qb){
			Connection conn= ConnectionManager.getConnection();
			try{
				PreparedStatement pstmt = conn.prepareStatement(
						"update qnaboard set qbtitle =?, qbcontent = ? where qbno=?"
				);
				pstmt.setString(1, qb.getQbtitle());
				pstmt.setString(2, qb.getQbcontent());
				pstmt.setInt(3, qb.getQbno());
				pstmt.executeUpdate();
				conn.close();
				
				return true;
			} catch(Exception e){
				e.printStackTrace();
				return false;
			}finally{
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
		}
		/*******
		 * 카운트 증가메소드
		 * @param qb
		 * @return*/
		public boolean updateReadCount(int qbno){
			Connection conn= ConnectionManager.getConnection();
			try{
				PreparedStatement pstmt = conn.prepareStatement(
						"update qnaboard set qbcounter=qbcounter+1 where qbno=?" 
				);
				pstmt.setInt(1, qbno);
				pstmt.executeUpdate();
				conn.close();
				
				return true;
			} catch(Exception e){
				e.printStackTrace();
				return false;
			}finally{
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
		}
		
		/*******
		 * 삭제 메소드
		 * @param qb
		 * @return*/
		public boolean delete(int qbno){
			Connection conn= ConnectionManager.getConnection();
			String sql1="delete from qnaboard where qbrefno="+qbno;
			String sql2="delete from qnaboard where qbno="+qbno;
			
			try{
				PreparedStatement pstmt1 = conn.prepareStatement(sql1);
				PreparedStatement pstmt2 = conn.prepareStatement(sql2);				
				pstmt1.executeUpdate();
				pstmt2.executeUpdate();

				return true;
			} catch(Exception e){
				e.printStackTrace();
				return false;
			}finally{
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
		}

		/*******
		 * 
		 * @param qb
		 * @return*/
		public QnaBoard  getQnaBoard(int qbno){
			QnaBoard qnaboard = null;
			Connection conn = ConnectionManager.getConnection();
			try {
				///////////////////////////////////////////////
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(
							"select *  from qnaboard where qbno="+qbno
						);
				if(rs.next()) {
					qnaboard = new QnaBoard();
					qnaboard.setQbno(rs.getInt(1));
					qnaboard.setMid(rs.getString(2));
					qnaboard.setQbtitle(rs.getString(3));
					qnaboard.setQbdate(rs.getString(4));
					qnaboard.setQbcontent(rs.getString(5));
					qnaboard.setQbcounter(rs.getInt(6));
					qnaboard.setQbrefno(rs.getInt(7));
				}
				rs.close();
				stmt.close();
				///////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return qnaboard;
		}	
		
		/*******
		 * 
		 * @param qb
		 * @return*/
		public Vector  getQnaBoardList(){
			Vector vector = new Vector();
			Connection conn = ConnectionManager.getConnection();
			try {
				///////////////////////////////////////////////
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(
						"select qbno, mid, qbtitle, qbdate, qbcontent, qbcounter from qnaboard"
				);
				while(rs.next()) {
					QnaBoard qnaboard = new QnaBoard();
					qnaboard.setQbno(rs.getInt(1));
					qnaboard.setMid(rs.getString(2));
					qnaboard.setQbtitle(rs.getString(3));
					qnaboard.setQbdate(rs.getString(4));
					qnaboard.setQbcontent(rs.getString(5));
					qnaboard.setQbcounter(rs.getInt(6));
					qnaboard.setQbrefno(rs.getInt(7));
					vector.add(qnaboard);
				}
				rs.close();
				stmt.close();
				///////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return vector;
		}	

		/*******
		 * 게시판 전체 줄수 구하는 메소드
		 * @param qb
		 * @return*/
		public long getTotalRows() {
			long result = 0;
			Connection conn = ConnectionManager.getConnection();
			try {
				///////////////////////////////////////////////
				PreparedStatement pstmt = conn.prepareStatement(
						"select count(*) from qnaboard"
				);
				ResultSet rs = pstmt.executeQuery();
				rs.next();
				result = rs.getLong(1);
				rs.close();
				pstmt.close();
				///////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return result;
		}	
		

		/*******
		 * 검색어에 대한 갯수뽑는 메소드
		 * @param qb
		 * @return*/
		public long getTotalSearch(String  qnasearch,  String qnasearchfield) {
			long result =0;
//			long str1 = rowsPerPage;
//			long str2= rowsPerPage * (currentPage - 1);
			Connection conn = ConnectionManager.getConnection();
			try {
				///////////////////////////////////////////////
				PreparedStatement pstmt = conn.prepareStatement(
						"select count(*) from qnaboard where " +qnasearch+" like '%"+qnasearchfield+"%'  "  				
				
				);
				ResultSet rs = pstmt.executeQuery();
				rs.next();
				result = rs.getLong(1);
				rs.close();
				pstmt.close();
				///////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return result;
		}	
		
			
		/*******
		 * 게시판 리스트 메소드
		 * @param qb
		 * @return*/
		public static Vector  getQnaBoardListPage(long currentPage, long rowsPerPage){
			Vector vector = new Vector();
			Connection conn = ConnectionManager.getConnection();
			//#*
			QnaBoardDAO qnaBoardDAO = new QnaBoardDAO(); 
			int rownum = (int)qnaBoardDAO.getTotalRows() + 1;
			int index = 0;	    
			long startindex = 0; 
			long endindex = 0;   
			startindex = (currentPage - 1) * rowsPerPage + 1;
			endindex = currentPage * rowsPerPage;
			//*#
			try {
				//@@long str1 = rowsPerPage;	//페이지당 글(행)수
				//@@long str2= rowsPerPage * (currentPage - 1);		
				
				Statement stmt = conn.createStatement();			
				/*@@ResultSet rs = stmt.executeQuery(
						"select  qnaboard.* " +
						" from " +
						" (select  qbno, mid, qbtitle, DATE_FORMAT(qnaboard.qbdate, '%Y-%m-%d ')qbdate, qbcontent, qbcounter, qbrefno, (@rownum:=@rownum+1) rownum" + 
							" from " +
							" (select qnaboard.*, (@rownum:=0) rnum" +
								" from qnaboard "+
							" ) qnaboard "+
						" ) qnaboard order by qbrefno desc limit  " + str2 +"," + str1 				
				);*/
				ResultSet rs = stmt.executeQuery(
					"select  *  from qnaboard order by qbrefno desc,qbno asc "		
				);
				while(rs.next()) {
					//#*
					index += 1;		
					rownum -= 1;	
					if(index >= startindex && index <= endindex){	//##
						QnaBoard qnaboard = new QnaBoard();
						qnaboard.setQbno(rs.getInt(1));
						qnaboard.setMid(rs.getString(2));
						qnaboard.setQbtitle(rs.getString(3));
						qnaboard.setQbdate(rs.getString(4));
						qnaboard.setQbcontent(rs.getString(5));
						qnaboard.setQbcounter(rs.getInt(6));
						qnaboard.setQbrefno(rs.getInt(7));
					//@@qnaboard.setRownum(rs.getInt(8));
						qnaboard.setRownum(rownum);	//##
						vector.add(qnaboard);
					}
				}
				rs.close();
				stmt.close();
				// /////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return vector;
		}
		//search에서 qbtitle,qbcontent,mid
		/*******
		 * 검색어에 대한 리스트 뽑는 메소드
		 * @param qb
		 * @return*/
		//@@ public static Vector  getSearch(long currentPage, long rowsPerPage, String qnasearchfield,String qnasearch){
		public Vector  getSearch(long currentPage, long rowsPerPage, String qnasearchfield,String qnasearch){
			
			//#*
			QnaBoardDAO qnaBoardDAO = new QnaBoardDAO(); 
			int rownum = (int)this.getTotalSearch(qnasearch, qnasearchfield) + 1;
			int index = 0;	    
			long startindex = 0; 
			long endindex = 0;   
			startindex = (currentPage - 1) * rowsPerPage + 1;
			endindex = currentPage * rowsPerPage;
			//*#
			Vector vector = new Vector();
			Connection conn = ConnectionManager.getConnection();
			 System.out.println("qnaserh = "+qnasearch+" qnasearchfield = "+qnasearchfield);
			try {
				long str1 = rowsPerPage;
				long str2= rowsPerPage * (currentPage - 1);
				Statement stmt = conn.createStatement();
//				
				
				/*@@ ResultSet rs = stmt.executeQuery(
						"select  qnaboard.* " +
						" from " +
						" (select  qbno, mid, qbtitle, DATE_FORMAT(qnaboard.qbdate, '%Y-%m-%d ')qbdate, qbcontent, qbcounter, qbrefno, (@rownum:=@rownum+1) rownum" + 
							" from " +
							" (select qnaboard.*, (@rownum:=0) rnum" +
								" from qnaboard "+
							" ) qnaboard "+
						" ) qnaboard where " +qnasearch+" like '%"+qnasearchfield+"%' order by qbrefno desc limit  " + str2 +"," + str1 	
				);*/
				ResultSet rs = stmt.executeQuery("select * from qnaboard"+
						" where "+qnasearch+" like '%"+qnasearchfield+"%' order by qbrefno desc, qbno asc");
				while(rs.next()) {
						//#*
						index += 1;		
						rownum -= 1;	
						if(index >= startindex && index <= endindex){	//*#
						QnaBoard qnaboard = new QnaBoard();
						qnaboard.setQbno(rs.getInt(1));
						qnaboard.setMid(rs.getString(2));
						qnaboard.setQbtitle(rs.getString(3));
						qnaboard.setQbdate(rs.getString(4));
						qnaboard.setQbcontent(rs.getString(5));
						qnaboard.setQbcounter(rs.getInt(6));
						qnaboard.setQbrefno(rs.getInt(7));
						qnaboard.setRownum(rownum);
						vector.add(qnaboard);
					}
				}
				rs.close();
				stmt.close();
				///////////////////////////////////////////////
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(conn!=null) try { conn.close(); } catch(Exception e) {}
			}
			return vector;
		}
		
		//next, preview
		/*******
		 * 이전글 뽑는 메소드
		 * @param qb
		 * @return*/
		public int getPreview(QnaBoard qb){
			int previewNumber = 0;
			Connection conn = null;
			try{
				conn = ConnectionManager.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(
						"select max(qbno) from (select qbno from qnaboard where qbno=qbrefno) qb where qbno<?"
				);
				pstmt.setInt(1, qb.getQbno());
				ResultSet rs = pstmt.executeQuery();
				
				int no = 0;
				while(rs.next()){
					no = rs.getInt(1);				
				}			 
				previewNumber = no!=0?no:qb.getQbno();
				
				return previewNumber;
			} catch (Exception e){
				e.printStackTrace();
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {				 
					e.printStackTrace();
				}
			}
			return qb.getQbno();
		}

		/*******
		 * 다음글 뽑는 메소드
		 * @param qb
		 * @return*/
		public int getNext(QnaBoard qb){
			int nextNumber = 0;
			String sql = "select min(qbno) from (select qbno from qnaboard where qbno=qbrefno) qb where qbno>?";
			
			Connection conn = null;
			try{
				conn = ConnectionManager.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, qb.getQbno());
				ResultSet rs = pstmt.executeQuery();
				
				int no = 0;
				while(rs.next()){
					no = rs.getInt(1);				
				}			 
				nextNumber = no!=0?no:qb.getQbno();
				
				return nextNumber;
			} catch (Exception e){
				e.printStackTrace();
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {				 
					e.printStackTrace();
				}
			}
			return qb.getQbno();
		}
	
		
}